package com.zl.learning.db.sqlite.sqlite;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.junit.Test;
import org.sqlite.SQLiteDataSource;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class DataSourceUtil {
    private QueryRunner queryRunner = new QueryRunner(getDataSource());

    public static DataSource getDataSource() {
        SQLiteDataSource dataSource = new SQLiteDataSource();
        dataSource.setUrl("jdbc:sqlite:test.db");
        dataSource.setEncoding("UTF-8");
        return dataSource;
    }

    @Test
    public void test1() throws SQLException {
        List<Map<String, Object>> res = queryRunner.query("select * from COMPANY", new MapListHandler());
        res.forEach(r -> r.forEach((k, v) -> System.out.println(k + "---" + v)));
    }

    @Test
    public void test2() {
        Object a = 133.3;
        float b = ((Number) a).floatValue();
        Integer c = 111;
        System.out.println(b);
    }

    @Test
    public void test3() throws SQLException {
        String sql = "CREATE TABLE COMPANY2 " +
                "(ID INT PRIMARY KEY     NOT NULL," +
                " NAME           TEXT    NOT NULL, " +
                " AGE            INT     NOT NULL, " +
                " ADDRESS        CHAR(50), " +
                " SALARY         REAL)";
        queryRunner.update(sql);
    }

    @Test
    public void test4() throws SQLException {
        String sql = "insert into COMPANY2 (ID,NAME,AGE,ADDRESS,SALARY)values(1,'zhansan',11,'中国',1000)";
        queryRunner.update(sql);
    }

    @Test
    public void test5() throws SQLException {
        String sql = "select * from COMPANY2";
        List<Map<String, Object>> list = queryRunner.query(sql, new MapListHandler());
        list.forEach(r -> r.forEach((k, v) -> System.out.println(k + "------" + v)));
    }
}
